package com.education.module.teacherExamResult;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.education.domain.Examdate;
import com.education.domain.LoginUser;
import com.education.domain.Teacher;
import com.education.domain.TeacherExamResult;
import com.education.domain.TeacherExamSetting;
import com.education.framework.base.BaseServices;
import com.education.framework.dao.IDao;
import com.education.framework.domain.SearchParams;
import com.education.framework.page.Page;
import com.education.framework.session.SessionHelper;

@Service
public class TeacherExamResultServices extends BaseServices implements IDao<TeacherExamResult>{

	@Override
	public List<TeacherExamResult> find(SearchParams searchParams, Page page) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT id,code,teacher_id,exam_date_id,kind_id,weight,exam_item_id,user_id,user_type,score,user_score FROM teacher_exam_result");
		String lp = " where ";
		List<Object> argsList = new ArrayList<Object>();
		if(null != searchParams){
			if(null != searchParams.get("title") && !"".equals((String)searchParams.get("title"))){
				sql.append(lp).append(" title like ? ");
				argsList.add("%" + searchParams.get("title") + "%");
				lp = " and ";
			}
		}
		Object[] args = argsList.toArray();
		List<TeacherExamResult> list = dao.query(pageSQL(sql.toString(),page),args,new TeacherExamResultRowmapper());
		page.setTotalItem(findRecordCount("select count(id) as cNum from teacher_exam_result ",new Object[]{}));
		return list;
	}
	
	public List<TeacherExamResult> find() {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT id,code,teacher_id,exam_date_id,kind_id,weight,exam_item_id,user_id,user_type,score,user_score FROM teacher_exam_result");
		
		List<TeacherExamResult> list = dao.query(sql.toString(),new TeacherExamResultRowmapper());
		return list;
	}
	
	public List<TeacherExamSetting> findResult(SearchParams searchParams, Page page) {
		LoginUser loginUser = SessionHelper.getInstance().getLoginUser();
		//int userid = SessionHelper.getInstance().getUser().getId();
		StringBuffer sql = new StringBuffer();
		sql.append("select te.id as batchId,tea.id as teacherId,tea.teacher_name,ed.id as examDateId,ed.startDate,ed.endDate, ");
		sql.append("case when ( select count(1) from teacher_exam_result ts ");
		sql.append("where ? = ts.user_id and te.id = ts.code and ts.user_score is not null ) >1 then '1' ELSE '0' END  as alreadyAssess,  ");
		sql.append("case when DATE_FORMAT(now(),'%Y-%m-%d') >= STR_TO_DATE(ed.startDate,'%Y-%m-%d') and DATE_FORMAT(now(),'%Y-%m-%d') <=STR_TO_DATE(ed.endDate,'%Y-%m-%d')  then '1' else '0' end as passTime  ");
		sql.append("from  teacher_exam_setting te  inner join teacher tea on tea.id = te.teacher_id ");
		sql.append("inner join examDate ed on ed.id = te.exam_date_id where ? in (select te3.user_id from teacher_exam_setting_3 te3 where te3.code = te.id and te3.user_type=? ) ");
		String lp = " and ";
		List<Object> argsList = new ArrayList<Object>();
		argsList.add(loginUser.getId());
		argsList.add(loginUser.getId());
		argsList.add(loginUser.getType());
		if(null != searchParams){
			if(null != searchParams.get("teacherName") && !"".equals((String)searchParams.get("teacherName"))){
				sql.append(lp).append(" tea.teacher_name like ? ");
				argsList.add("%" + searchParams.get("teacherName") + "%");
				lp = " and ";
			}
			if(null != searchParams.get("examDate") && !"".equals((String)searchParams.get("examDate"))){
				sql.append(lp).append(" te.exam_date_id = ? ");
				argsList.add(searchParams.get("examDate"));
				lp = " and ";
			}
		}
		Object[] args = argsList.toArray();
		List<TeacherExamSetting> list = dao.query(pageSQL(sql.toString(),page),args,new TeacherExamSettingRowmapper());
		
		StringBuffer sql1 = new StringBuffer();
		sql1.append("select count(*) from  teacher_exam_setting te  inner join teacher tea on tea.id = te.teacher_id  ");
		sql1.append("inner join examDate ed on ed.id = te.exam_date_id where ? in (select te3.user_id from teacher_exam_setting_3 te3 where te3.code = te.id and te3.user_type=? )  ");
		page.setTotalItem(findRecordCount(sql1.toString(),new Object[]{loginUser.getId(),loginUser.getType()}));
		return list;
	}
	
	
	public List<TeacherExamResult> findResultDetail(TeacherExamResult teacherExamResult) {
		LoginUser loginUser = SessionHelper.getInstance().getLoginUser();
		StringBuffer sql = new StringBuffer();
		sql.append("select tes.id,ed.startDate as startDate,ed.endDate as endDate,ed.description as edName,tea.teacher_name as teaName ,kind.kind_name as kindName,tei.`name` as assessName, ");
		sql.append("ei.item_name as itemName,ei.description as description,tes.score as totalScore from teacher_exam_result tes ");
		sql.append("inner join examDate ed on ed.id = tes.exam_date_id ");
		sql.append("inner join teacher tea on tea.id = tes.teacher_id ");
		sql.append("inner join kind on kind.id = tes.kind_id ");
		sql.append("inner join teacher_exam_setting tei on tei.id = tes.`code` ");
		sql.append("inner join exam_item ei on ei.id = tes.exam_item_id ");
		sql.append("where tes.teacher_id =? and tes.exam_date_id= ? and tes.user_id =? and tes.user_type=? and  tes.`code`=? order by tes.kind_id ");
		List<Object> argsList = new ArrayList<Object>();
		argsList.add(teacherExamResult.getTeacherId());
		argsList.add(teacherExamResult.getExamDateId());
		argsList.add(loginUser.getId());
		argsList.add(loginUser.getType());
		argsList.add(teacherExamResult.getCode());
		Object[] args = argsList.toArray();
		List<TeacherExamResult> list = dao.query(sql.toString(),args,new TeacherExamResultDeatilRowmapper());
		return list;
	}
	
	private class TeacherExamResultDeatilRowmapper implements RowMapper<TeacherExamResult> {
		@Override
		public TeacherExamResult mapRow(ResultSet rs, int arg1) throws SQLException {
			TeacherExamResult obj = new TeacherExamResult();
			obj.setId(rs.getInt("id"));
			obj.setStartDate(rs.getString("startDate"));
			obj.setEndDate(rs.getString("endDate"));
			obj.setTeacherName(rs.getString("teaName"));
			obj.setKindName(rs.getString("kindName"));
			obj.setAssessName(rs.getString("assessName"));
			obj.setExamItemName(rs.getString("itemName"));
			obj.setItemDescription(rs.getString("description"));
			obj.setScore(rs.getInt("totalScore"));
			obj.setEdName(rs.getString("edName"));
			return obj;
		}
	}
	public List<Examdate> findExamDate() {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT id,code,startDate,endDate,description FROM examDate");
		
		List<Examdate> list = dao.query(sql.toString(),new ExamdateRowmapper());
		return list;
	}
	
	private class ExamdateRowmapper implements RowMapper<Examdate> {
		@Override
		public Examdate mapRow(ResultSet rs, int arg1) throws SQLException {
			Examdate obj = new Examdate();
			obj.setId(rs.getInt("id")); 
			obj.setStartdate(rs.getString("startDate")); 
			obj.setDescription(rs.getString("description")); 
			obj.setEnddate(rs.getString("endDate")); 
			obj.setCode(rs.getString("code")); 
			return obj;
		}
	}
	

	@Override
	public int save(TeacherExamResult obj) {
		 
 StringBuffer sql = new StringBuffer(); 
 sql.append("insert into teacher_exam_result ( "); 
 sql.append("id,code,teacher_id,exam_date_id,kind_id "); 
 sql.append(",weight,exam_item_id,user_id,user_type,score "); 
 sql.append(",user_score ");  
 sql.append(" ) values(?,?,?,?,?,?,?,?,?,?,?) "); 
 Object[] args = {obj.getId(),obj.getCode(),obj.getTeacherId(),obj.getExamDateId(),obj.getKindId(),obj.getWeight() 
 ,obj.getExamItemId(),obj.getUserId(),obj.getUserType(),obj.getScore(),obj.getUserScore() };
		 
		 dao.update(sql.toString(), args);
		return dao.queryForInt("SELECT LAST_INSERT_ID()"); 
	}

	@Override
	public TeacherExamResult findForObject(Integer id) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT id,code,teacher_id,exam_date_id,kind_id,weight,exam_item_id,user_id,user_type,score,user_score FROM teacher_exam_result ");
		sql.append(" where id=? ");
		
		Object[] args = {id};
		return dao.queryForObject(sql.toString(),args,new TeacherExamResultRowmapper());
	}
	
	
	public void updateUserScore(TeacherExamResult teacherExamResult) {
		 StringBuffer sql = new StringBuffer(); 
		 sql.append("update teacher_exam_result t set t.user_score =? where t.id=? "); 
		 Object[] args = {teacherExamResult.getUserScore(),teacherExamResult.getId() };
		 dao.update(sql.toString(), args);
	}
	
	

	@Override
	public void update(TeacherExamResult obj) {
	 StringBuffer sql = new StringBuffer(); 
	 sql.append("update teacher_exam_result "); 
	 sql.append("set  "); 
	 sql.append("code=?,teacher_id=?,exam_date_id=?,kind_id=?,weight=? "); 
	 sql.append(",exam_item_id=?,user_id=?,user_type=?,score=?,user_score=? where id=?  "); 
	 Object[] args = {obj.getCode(),obj.getTeacherId(),obj.getExamDateId(),obj.getKindId(),obj.getWeight(),obj.getExamItemId() 
	 ,obj.getUserId(),obj.getUserType(),obj.getScore(),obj.getUserScore(),obj.getId() };
			 
			 dao.update(sql.toString(), args);
	}

	@Override
	public void delete(Integer id) {
		String sql = "delete from teacher_exam_result where id=?";
		dao.update(sql, new Object[]{id});
	}

	@Override
	public boolean findIsExist(String name) {
		return false;
	}
	
	private class TeacherExamResultRowmapper implements RowMapper<TeacherExamResult> {
		@Override
		public TeacherExamResult mapRow(ResultSet rs, int arg1) throws SQLException {
			TeacherExamResult obj = new TeacherExamResult();
			obj.setId(rs.getInt("id")); 
obj.setUserScore(rs.getInt("user_score")); 
obj.setWeight(rs.getFloat("weight")); 
obj.setUserType(rs.getInt("user_type")); 
obj.setScore(rs.getInt("score")); 
obj.setUserId(rs.getInt("user_id")); 
obj.setExamItemId(rs.getInt("exam_item_id")); 
obj.setKindId(rs.getInt("kind_id")); 
obj.setTeacherId(rs.getString("teacher_id")); 
obj.setCode(rs.getInt("code")); 
obj.setExamDateId(rs.getInt("exam_date_id")); 

			return obj;
		}
	}
	
	private class TeacherExamSettingRowmapper implements RowMapper<TeacherExamSetting> {
		@Override
		public TeacherExamSetting mapRow(ResultSet rs, int arg1) throws SQLException {
			TeacherExamSetting obj = new TeacherExamSetting();
			obj.setBatchId(rs.getInt("batchId"));
			obj.setAlreadyAssess(rs.getString("alreadyAssess"));
			obj.setPassTime(rs.getString("passTime"));
			Teacher tea = new Teacher();
			tea.setId(rs.getInt("teacherId"));
			tea.setTeacherName(rs.getString("teacher_name"));
			Examdate ed =new Examdate();
			ed.setId(rs.getInt("examDateId"));
			ed.setStartdate(rs.getString("startdate"));
			ed.setEnddate(rs.getString("enddate"));
			obj.setTeacher(tea);
			obj.setExamDate(ed);
			return obj;
		}
	}
	
	

}
